package org.example.dao;

import org.example.model.Product;
import org.example.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDAOImpl implements ProductDao1 {
    private Product mapResultSetToProduct(ResultSet rs) throws SQLException {
        Product product = new Product();
        product.setProductId(rs.getInt("product_id"));
        product.setName(rs.getString("product_name"));
        product.setDescription(rs.getString("description"));
        product.setPrice(rs.getBigDecimal("price"));
        product.setStock(rs.getInt("stock"));
        product.setCategoryId(rs.getInt("category_id"));
        product.setBrand(rs.getString("brand"));
        product.setStatus(rs.getString("status"));
        product.setImage(rs.getString("image"));
        product.setDiscount(rs.getDouble("virtual_discount"));
        product.setSales(0);
        product.setCreateTime(rs.getTimestamp("create_time"));
        return product;
    }

    @Override
    public List<Product> findProductsByDiscount(int limit) {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT *, 0.5 as virtual_discount FROM tb_product " +
                    "WHERE status = '上架' " +
                    "ORDER BY RAND() LIMIT ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                products.add(mapResultSetToProduct(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }
    
    @Override
    public List<Product> findNewProducts(int limit) {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT *, 0.8 as virtual_discount FROM tb_product " +
                    "WHERE status = '上架' " +
                    "ORDER BY create_time DESC, RAND() LIMIT ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                products.add(mapResultSetToProduct(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }
    
    @Override
    public List<Product> findHotProducts(int limit) {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT *, 0.7 as virtual_discount FROM tb_product " +
                    "WHERE status = '上架' " +
                    "ORDER BY RAND() LIMIT ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                products.add(mapResultSetToProduct(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }

    @Override
    public Product findById(int productId) {
        Product product = null;
        String sql = "SELECT * FROM tb_product WHERE product_id = ?";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, productId);
            ResultSet rs = stmt.executeQuery();
            
            if (rs.next()) {
                product = new Product();
                product.setProductId(rs.getInt("product_id"));
                product.setName(rs.getString("product_name"));
                product.setDescription(rs.getString("description"));
                product.setPrice(rs.getBigDecimal("price"));
                product.setStock(rs.getInt("stock"));
                product.setCategoryId(rs.getInt("category_id"));
                product.setBrand(rs.getString("brand"));
                product.setStatus(rs.getString("status"));
                product.setImage(rs.getString("image"));
                // 设置默认折扣为1.0（不打折）
                product.setDiscount(1.0);
                // 如果是秒杀商品，设置0.5折扣
                if (isFlashSaleProduct(productId)) {
                    product.setDiscount(0.5);
                }
                // 如果是新品，设置0.8折扣
                else if (isNewProduct(productId)) {
                    product.setDiscount(0.8);
                }
                // 如果是热卖商品，设置0.7折扣
                else if (isHotProduct(productId)) {
                    product.setDiscount(0.7);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return product;
    }
    
    // 辅助方法：检查是否为秒杀商品
    private boolean isFlashSaleProduct(int productId) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(
                 "SELECT COUNT(*) FROM tb_product WHERE product_id = ? AND status = '上架' ORDER BY RAND() LIMIT 4")) {
            stmt.setInt(1, productId);
            ResultSet rs = stmt.executeQuery();
            return rs.next() && rs.getInt(1) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    
    // 辅助方法：检查是否为新品
    private boolean isNewProduct(int productId) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(
                 "SELECT COUNT(*) FROM tb_product WHERE product_id = ? AND status = '上架' ORDER BY create_time DESC LIMIT 4")) {
            stmt.setInt(1, productId);
            ResultSet rs = stmt.executeQuery();
            return rs.next() && rs.getInt(1) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    
    // 辅助方法：检查是否为热卖商品
    private boolean isHotProduct(int productId) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(
                 "SELECT COUNT(*) FROM tb_product WHERE product_id = ? AND status = '上架' ORDER BY RAND() LIMIT 4")) {
            stmt.setInt(1, productId);
            ResultSet rs = stmt.executeQuery();
            return rs.next() && rs.getInt(1) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
} 